﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utils;
using DataHelper;

namespace DAL
{
   public class EmpInsuranceDao
    {

        #region 保险信息

        public bool Insert(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().InsertTable(tableName, sdList);
        }


        public bool Update(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().UpdateTable(tableName, sdList);
        }

        public bool DeleteOneInfo(string insuranceID)
        {
            return SQLHelper.ExecuteSqlWithTransaction("Delete from Admin_Insurance Where  InsuranceID=" + insuranceID);
        }

        public DataTable GetOneInfo(string insuranceID)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select * from View_Insurance Where  InsuranceID=" + insuranceID);
        }

        //获取有效保险单的ID, 根据员工编号
       //获取的是第一个
        public string GetIdByEmpcode(string empcode)
        {
            DataTable dt =
             SQLHelper.ExecuteSqlGetDrToDt("Select top 1 InsuranceID from View_Insurance Where Statu in ('Y','T') and  EmpCode='" + empcode + "'");
            string insuranceID = "";
            if (dt.Rows.Count > 0)
            {
                insuranceID = dt.Rows[0]["InsuranceID"].ToString();
            }
            return insuranceID;
        }


        public string GetSQL(string serWord, string bTime, string eTime, string statu, string timeType)
        {
            StringBuilder strSQL = new StringBuilder("Select * From View_Insurance Where 1=1 ");
            if (!string.IsNullOrWhiteSpace(serWord))
                strSQL.Append(" And (EmpCode='" + serWord + "' Or EmpName Like '%" + serWord + "%')");
            else
            {
                if (statu == "")
                    strSQL.Append(" and Statu ='Y' ");
                else
                {
                    if (statu != "A")
                        strSQL.Append(" and Statu ='" + statu + "' ");
                }
                switch (timeType)
                {
                    case "CheckTime":
                        if (RegexRule.IsDate(bTime))
                            strSQL.Append(" And CheckTime>='" + bTime + "'");
                        if (RegexRule.IsDate(eTime))
                            strSQL.Append(" And CheckTime<='" + eTime + "'");
                        break;
                    case "BeginTime":
                        if (RegexRule.IsDate(bTime))
                            strSQL.Append(" And BeginTime>='" + bTime + "'");
                        if (RegexRule.IsDate(eTime))
                            strSQL.Append(" And BeginTime<='" + eTime + "'");
                        break;
                    case "EndTime":
                        if (RegexRule.IsDate(bTime))
                            strSQL.Append(" And EndTime>='" + bTime + "'");
                        if (RegexRule.IsDate(eTime))
                            strSQL.Append(" And EndTime<='" + eTime + "'");
                        break;
                    case "InputTime":
                        if (RegexRule.IsDate(bTime))
                            strSQL.Append(" And InputTime>='" + bTime + "'");
                        if (RegexRule.IsDate(eTime))
                            strSQL.Append(" And InputTime<='" + eTime + "'");
                        break;
                }
            }
            strSQL.Append(" Order by InsuranceNo ASC");
            return strSQL.ToString();
        }
        public DataTable GetLists(string serWord, string bTime, string eTime, string statu, string timeType)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(GetSQL(serWord, bTime, eTime, statu, timeType));
            return dt;
        }
       //终止自动到期的保险信息
       //停止使用 2011-09-02 
        public bool UpdateToEnd()
        {
            DateTime dTime = System.DateTime.Now;
            
            StringBuilder strSQL = new StringBuilder("Update Admin_Employee set SafetyAmount=0 Where EmpCode in (Select EmpCode From Admin_Insurance Where  Statu in ('Y','T') and EndTime<='" + dTime.AddDays(-30).ToString("yyyy-MM-dd") + "');");
            strSQL.Append("Update Admin_Insurance Set Statu='N',StatuTime='" + dTime + "' Where Statu in ('Y','T') and EndTime<='" + dTime.AddDays(-30).ToString("yyyy-MM-dd") + "';");
          return  SQLHelper.ExecuteSqlWithTransaction(strSQL.ToString());
        }
        //终止或开启某个员工所有的保险信息，终止时间为endTime
        public bool UpdateToEnd(string empCode,string endTime,string statu)
        {
            DateTime dTime = System.DateTime.Now;
            return SQLHelper.ExecuteSqlWithTransaction("Update Admin_Employee set SafetyAmount=0 Where EmpCode='" + empCode + "';Update Admin_Insurance Set Statu='" + statu + "',EndTime='" + endTime + "',StatuTime='" + dTime + "' Where Statu<>'N' and EmpCode='" + empCode + "'");
        }
        #endregion
    }
}
